
*For Margaret dropbox folders 
global NCREIF "/Users/BeckaBrolinson/Dropbox/NCREIF/data" 
global build 	"$NCREIF/build" 
global analysis "$NCREIF/analysis"
global results 	"$analysis/results"
global figures 	"$analysis/figures" 

*------------------------------------------------------------------------------*
*	Step 12- Make Summary Statistics Tables	   *
*------------------------------------------------------------------------------*	


/*
This do-file is a template to create  summary and balance tables using Stata.
The current structure create a basic balance table with two panels.
It can easily be ammended to contain other statistics instead of differences in means.
The structure is based on original code by John de Quidt.
*/
use "$build/01_annualizeddatawcontrol.dta", replace

	gen sqft1000s = sqft / 1000 
	gen HDD1000s = HDD / 1000 
	gen CDD1000s = CDD / 1000 
	gen used_space1000s = used_space/ 1000
	gen real_capex_ti_sqft = real_capex_ti / sqft 
	gen real_capex_bldimp_sqft = real_capex_bldimp / sqft 
	


* Step 1: Label variables with the text you want to appear in the table *

	label var interaction "Cert*Post" 
	label var treat "1[treat=1]"
	label var logrealrentpersf "ln(Rent/Sq. Ft.) (\\$)"
	label var Covered_E "Benchmarking Law" 
	label var real_elecprice "Avg. Elec. Price (\\\$/MWh)" 
	label var real_gasprice "Avg. Gas Price (\\\$/Mcf)" 
	label var Unemployment "Unemployment" 
	label var age "Building Age" 
	label var age2 "Building Age Squared" 
	label var used_space "Used Space (Pct. Leased * Sq. Ft.)"
	label var used_space1000s "Used Space (Pct. Leased * Sq. Ft.) (1000s)"
	labe var percentleased "Percent Leased (\%)"
	label var real_capex_ti "Cap Exp. (Tenant Imp.)" 
	label var real_capex_ti_sqft "Cap Exp./Sq. Ft. (Tenant Imp.) (\\$)"
	label var real_capex_bldimp "Cap Exp. (Bldg. Imp.)"
	label var real_capex_bldimp_sqft "Cap Exp./Sq. Ft. (Bldg. Imp.) (\\$)"
	label var HDD "HDD" 
	label var HDD1000s "HDD (1000s)" 
	label var CDD "CDD" 
	label var CDD1000s "CDD (1000s)" 
	label var rl_yr_rentpersf "Rent per Sq. Ft."
	label var logrealutilpersf "ln(Util/Sq. Ft.) (\\$)"
	label var rl_yr_rentpersf "Rent per Sq. Ft."
	label var rl_yr_utilpersf "Util. per Sq. Ft."
	label var yrbuilt "Year Built" 
	label var sqft "Square Feet"
	label var sqft1000s "Square Feet (1000s)" 
	

* Step  2: Create variables that indicate empty lines or Panel headings *

	*Empty line
	gen empt1=.
	label var empt1 " "
	gen empt2=.
	label var empt2 " "

	*Panel A

	gen pana = .
	label var pana "\textbf{Pooled} \\" // Note that you can use Latex code in the tables. The '\\' adds an additional empty line below the panel heading.

	*gen panb = .
	*label var panb "\textbf{Post} \\" //


* Step 3: Create conditional variables to be used in panels

* I want panel b to be women only. I create conditonal variables for this.

local temp_var "empt1 empt2 pana" //Local capturing variables to be deleted after this code


*foreach x in rl_yr_utilpersf {
*clonevar  `x'b= `x' if post==1 // Use clonevar instead of gen to preserve label

*local temp_var = "`temp_var' `x'b"
*}

* Step 4: Full list of covariates (including temporary ones in the desired order) *
	loc covars "rl_yr_utilpersf rl_yr_rentpersf sqft1000s yrbuilt Covered_E real_elecprice real_gasprice Unemployment HDD1000s CDD1000s used_space1000s percentleased real_capex_ti_sqft real_capex_bldimp_sqft"


* Step 5: Choose number of columns in table (which statistics do you want to display).

	local number_columns= 4 // This determines the number of columns (4 in this case).
	
	
/* This creates empty regression results for each column */

*No need to touch this code*
qui {
    preserve
	
    clear all
    eststo clear
    estimates drop _all

    set obs 10
    qui gen x = 1
    qui gen y = 1

    forval i = 1/`number_columns' { 
        qui eststo col`i': reg x y
    }
    restore
	}
******************************************************************	
	

	
* Step 6: Define table headings

    loc colnames " "\shortstack{Mean Uncertified\\Pre-2009}" "\shortstack{Mean Uncertified\\Post-2009}" "\shortstack{Mean Certified\\Pre-Cert}" "\shortstack{Mean Certified\\Post-Cert}" "
    loc colnames2 ""Pre-2009" "Post-2009" "Pre-Cert" "Post-Cert" "

*Define empty locals for labels and outout - no need to change
    loc rowstats ""
    loc rowlabels ""


* Step 7: Add statistics to regression results

	
    foreach var in `covars' { // Loop over all covariates
	
	
	qui sum `var'   // Check if variable is non-empty
	if `r(N)'==0 {
	estadd loc `var'_m = " ": col1 // Ensure that the estimation result exists 
	}
	if `r(N)'!=0 { // Only continue if variable non-empty

/* Here you need to choose what statistics you want to be displayed in the table. 
This can include regression coefficients, summary statistics,  p-values, or strings.
*/
		* Add summary statistic
       qui {
            qui sum `var'  if treat==0 & year<=2009 
            estadd loc `var'_m = string(r(mean), "%9.3f"): col1   // %9.2f determines the decimals displayed
			estadd loc `var'_sd = "(" + string(r(sd), "%9.3f") + ")": col1   // %9.2f determines the decimals displayed
        }
		
		qui {
            qui sum `var'  if treat==0 & year>2009 
            estadd loc `var'_m = string(r(mean), "%9.3f"): col2   // %9.2f determines the decimals displayed
			estadd loc `var'_sd = "(" + string(r(sd), "%9.3f") + ")": col2   // %9.2f determines the decimals displayed
        }
		
		qui {
            qui sum `var'  if treat==1 & post==0 
            estadd loc `var'_m = string(r(mean), "%9.3f"): col3   // %9.2f determines the decimals displayed
			estadd loc `var'_sd = "(" + string(r(sd), "%9.3f") + ")": col3   // %9.2f determines the decimals displayed
        }
		
        qui {
            qui sum `var'  if treat==1 & post==1 
            estadd loc `var'_m = string(r(mean), "%9.3f"): col4   // %9.2f determines the decimals displayed
			estadd loc `var'_sd = "(" + string(r(sd), "%9.3f") + ")": col4   // %9.2f determines the decimals displayed
        }
		
		* Add regression coefficients
		*qui {
        *   qui reg `var' treat, robust
        *  estadd loc `var'_m = string(_b[treat], "%9.2f"): col3
        *}
		}
        loc rowstats "`rowstats' `var'_m `var'_sd" // This statistics to the rowstats local which contains all statistics to be included in the table.
        loc rowlabels "`rowlabels' `"`: var la `var''"' " " " // This add labels to the rowlabels local
	}
	
/*
Step 7.1: Add numer of observations at the bottom of the table (below any column).
This is manually to only contain values in the first two columns but not the other ones.

*/
	
	        cap noi {
            qui sum treat  if treat==0 & year<=2009 
            estadd loc numobs = string(r(N), "%9.0fc"): col1  
        }
		cap noi {
            qui sum treat  if treat==0 & year>2009 
            estadd loc numobs = string(r(N), "%9.0fc"): col2 
        }
		
		cap noi {
            qui sum treat  if treat==1 & post==0
            estadd loc numobs = string(r(N), "%9.0fc"): col3  
        }
		
		cap noi {
            qui sum treat  if treat==1 & post==1
            estadd loc numobs = string(r(N), "%9.0fc"): col4  
        }

		loc rowstats "`rowstats' numobs" // Note that this is adjusted to num
        loc rowlabels "`rowlabels'  "N" " " " 
		
		
 
* Step 8: Produce outputs (only need to adjust the output path)

  
    esttab * using "$results/10_summarystatistics.tex",  label replace booktabs ///
	alignment(SSSS) ///
	nonotes nogaps /// removes notes from bottom of table 
	cells(none) /// removes y and constant from top of table 
	title(Summary Statistics by Certification Status\label{tab10summarystatistics}) ///
	nonum compress noobs nobaselevels  mtitle(`colnames')  stats(`rowstats', labels(`rowlabels')) ///
	addnote("Standard deviations are reported in parenthesis. Each column reports the annual average and the standard deviation over two time periods--before" ///
						"certification and after certification. For buildings that are never certified we define before certification as before or in 2009 and after certification as after" /// 
						"2009.")

		

* Drop temporarily stored variables (no need to change)
    eststo clear
	drop `temp_var'



